
import com.alibaba.excel.util.StringUtils;
import com.alibaba.fastjson.JSONObject;
import com.microsoft.schemas.office.visio.x2012.main.CellType;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.StringUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.net.URL;
import java.time.LocalDateTime;
import java.util.*;

/**
 * @program: gfx-hk-soa
 * @description:
 * @author: Jay
 * @create: 2020-08-20 17:10
 **/
public class ExportExcelUtil {
    private static final String[] cellList = {"型号", "SKU", "货号", "数量arriveDateEast", "总体积East/立方米", "售罄日期arriveDateEast",
            "数量arriveDateMid", "总体积Mid/立方米", "售罄日期arriveDateMid", "数量arriveDateWest", "总体积West/立方米", "售罄日期arriveDateWest"};


    public static void main(String[] args) {
        try {
            //readSkuInfo();
            readSkuInfo();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static void writeWithTemplate(String filePath, List<SkuCalcDate> list) {
        // POIFSFileSystem
        try {
            if (CollectionUtils.isEmpty(list)) {
                return;
            }
            // 构建XSSFWorkbook
            XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
            XSSFSheet sheet1 = hssfWorkbook.createSheet("sheet1");
            //抬头
            XSSFRow row1 = sheet1.createRow(0);
            for (int j = 0; j < cellList.length; j++) {
                XSSFCell cell1 = row1.createCell(j);
                cell1.setCellValue(cellList[j]);
                //cell1.setCellType(CellType.GREATER_THAN);
            }

            for (int i = 0; i < list.size(); i++) {
                int row = i + 1;
                SkuCalcDate skuCalcDate = list.get(i);
                XSSFRow xssfRow = sheet1.createRow(row);
                for (int j = 0; j < cellList.length; j++) {
                    XSSFCell xssfCell = xssfRow.createCell(j);
                    if (j == 0) {
                        xssfCell.setCellValue(skuCalcDate.getModelName());
                    } else if (j == 1) {
                        xssfCell.setCellValue(skuCalcDate.getSku());
                    } else if (j == 2) {
                        xssfCell.setCellValue(skuCalcDate.getArtNo());
                        //下面返回结果
                    } else if (j == 3) {
                        xssfCell.setCellValue(skuCalcDate.getSumEast());
                    } else if (j == 4) {
                        xssfCell.setCellValue(skuCalcDate.getVolumeEast());
                    } else if (j == 5) {
                        xssfCell.setCellValue(skuCalcDate.getArriveDateEast());
                    } else if (j == 6) {
                        xssfCell.setCellValue(skuCalcDate.getSumMid());
                    } else if (j == 7) {
                        xssfCell.setCellValue(skuCalcDate.getVolumeMid());
                    } else if (j == 8) {
                        xssfCell.setCellValue(skuCalcDate.getArriveDateMid());
                    } else if (j == 9) {
                        xssfCell.setCellValue(skuCalcDate.getSumWest());
                    } else if (j == 10) {
                        xssfCell.setCellValue(skuCalcDate.getVolumeWest());
                    } else if (j == 11) {
                        xssfCell.setCellValue(skuCalcDate.getArriveDateWest());
                    }
                }

            }


            // 把加密后的文件写回到流
            FileOutputStream fos = new FileOutputStream(filePath);
            hssfWorkbook.write(fos);
            fos.close();
        } catch (Throwable e) {
            e.printStackTrace();
        }

    }

    public static HashMap<String, SkuOnTheWayInfo> readSkuOnTheWayEast() throws IOException {
        String fileName = "E:\\github_java\\skuCalcDate\\src\\main\\resources\\skuCalc.xlsx";
        FileInputStream fis = new FileInputStream(new File(fileName));
        XSSFWorkbook fw = new XSSFWorkbook(fis);
        // 获取第一个Sheet
        XSSFSheet sheet = fw.getSheetAt(1);
        //List<SkuCalcDate> list = new ArrayList<>();
        HashMap<String, SkuOnTheWayInfo> map = new HashMap();

        //XSSFRow titleRow = sheet.getRow(1);
        for (Row row : sheet) {
            if (row.getRowNum() >= 2) {
                String modelName = row.getCell(0).getStringCellValue();
                String sku = row.getCell(1).getStringCellValue();
                if (StringUtils.isBlank(sku)) {
                    continue;
                }
                String artNo = row.getCell(2).getStringCellValue();
                Double stockOntheWayEast1 = 0D;
                if (checkValue(row.getCell(3).toString())) {
                    stockOntheWayEast1 = Double.parseDouble(row.getCell(3).toString());
                }

                Double stockOntheWayEast2 = 0D;
                if (checkValue(row.getCell(4).toString())) {
                    stockOntheWayEast2 = Double.parseDouble(row.getCell(4).toString());
                }

                Double stockOntheWayEast3 = 0D;
                if (checkValue(row.getCell(5).toString())) {
                    stockOntheWayEast3 = Double.parseDouble(row.getCell(5).toString());
                }
                Double stockOntheWayEast4 = 0D;
                if (checkValue(row.getCell(6).toString())) {
                    stockOntheWayEast4 = Double.parseDouble(row.getCell(6).toString());
                }
                Double stockOntheWayEast5 = 0D;
                if (checkValue(row.getCell(7).toString())) {
                    stockOntheWayEast5 = Double.parseDouble(row.getCell(7).toString());
                }
                Double stockOntheWayEast6 = 0D;
                if (checkValue(row.getCell(8).toString())) {
                    stockOntheWayEast6 = Double.parseDouble(row.getCell(8).toString());
                }
                Double stockOntheWayEast7 = 0D;
                if (checkValue(row.getCell(9).toString())) {
                    stockOntheWayEast7 = Double.parseDouble(row.getCell(9).toString());
                }
                Double sumStockEast = stockOntheWayEast1 + stockOntheWayEast2 + stockOntheWayEast3 + stockOntheWayEast4 + stockOntheWayEast5 + stockOntheWayEast6 + stockOntheWayEast7;

                SkuOnTheWayInfo skuCalcDate = SkuOnTheWayInfo.builder().modelName(modelName).sku(sku).artNo(artNo).sumStockEast(sumStockEast)
                        .build();

                map.put(sku, skuCalcDate);
            }

        }

        fis.close();

        System.out.println(JSONObject.toJSONString(map));
        return map;
    }


    public static HashMap<String, SkuOnTheWayInfo> readSkuOnTheWayWest() throws IOException {
        String fileName = "E:\\github_java\\skuCalcDate\\src\\main\\resources\\skuCalc.xlsx";
        FileInputStream fis = new FileInputStream(new File(fileName));
        XSSFWorkbook fw = new XSSFWorkbook(fis);
        // 获取第一个Sheet
        XSSFSheet sheet = fw.getSheetAt(2);
        //List<SkuCalcDate> list = new ArrayList<>();
        HashMap<String, SkuOnTheWayInfo> map = new HashMap();

        //XSSFRow titleRow = sheet.getRow(1);
        for (Row row : sheet) {
            if (row.getRowNum() >= 2) {
                String modelName = row.getCell(0).getStringCellValue();
                String sku = row.getCell(1).getStringCellValue();
                if (StringUtils.isBlank(sku)) {
                    continue;
                }
                String artNo = row.getCell(2).getStringCellValue();
                Double stockOntheWayWest1 = 0D;
                if (checkValue(row.getCell(3).toString())) {
                    stockOntheWayWest1 = Double.parseDouble(row.getCell(3).toString());
                }

                Double stockOntheWayWest2 = 0D;
                if (checkValue(row.getCell(4).toString())) {
                    stockOntheWayWest2 = Double.parseDouble(row.getCell(4).toString());
                }

                Double stockOntheWayWest3 = 0D;
                if (checkValue(row.getCell(5).toString())) {
                    stockOntheWayWest3 = Double.parseDouble(row.getCell(5).toString());
                }
                Double stockOntheWayWest4 = 0D;
                if (checkValue(row.getCell(6).toString())) {
                    stockOntheWayWest4 = Double.parseDouble(row.getCell(6).toString());
                }
                Double stockOntheWayWest5 = 0D;
                if (checkValue(row.getCell(7).toString())) {
                    stockOntheWayWest5 = Double.parseDouble(row.getCell(7).toString());
                }
                Double stockOntheWayWest6 = 0D;
                if (checkValue(row.getCell(8).toString())) {
                    stockOntheWayWest6 = Double.parseDouble(row.getCell(8).toString());
                }
                Double stockOntheWayWest7 = 0D;
                if (checkValue(row.getCell(9).toString())) {
                    stockOntheWayWest7 = Double.parseDouble(row.getCell(9).toString());
                }
                Double sumStockWest = stockOntheWayWest1 + stockOntheWayWest2 + stockOntheWayWest3 + stockOntheWayWest4 +
                        stockOntheWayWest5 + stockOntheWayWest6 + stockOntheWayWest7;

                SkuOnTheWayInfo skuCalcDate = SkuOnTheWayInfo.builder().modelName(modelName).sku(sku).artNo(artNo).sumStockWest(sumStockWest)
                        .build();
                map.put(sku, skuCalcDate);
            }

        }

        fis.close();

        System.out.println(JSONObject.toJSONString(map));
        return map;
    }


    public static List<SkuCalcDate> readSkuInfo() throws IOException {
        String fileName = "E:\\github_java\\skuCalcDate\\src\\main\\resources\\skuCalc.xlsx";
        FileInputStream fis = new FileInputStream(new File(fileName));
        XSSFWorkbook fw = new XSSFWorkbook(fis);
        // 获取第一个Sheet
        XSSFSheet sheet = fw.getSheetAt(0);
        List<SkuCalcDate> list = new ArrayList<>();
        //HashMap<String, Object> map = new HashMap();

        XSSFRow titleRow = sheet.getRow(1);
        for (Row row : sheet) {
            if (row.getRowNum() >= 1) {
                String modelName = row.getCell(0).getStringCellValue();
                String sku = row.getCell(1).getStringCellValue();
                if (StringUtils.isBlank(sku)) {
                    continue;
                }
                String artNo = row.getCell(2).getStringCellValue();
                Double length = row.getCell(3).getNumericCellValue();
                Double width = row.getCell(4).getNumericCellValue();
                Double height = row.getCell(5).getNumericCellValue();
                Double stockEast = null;

                if (checkValue(row.getCell(6).toString())) {
                    stockEast = Double.parseDouble(row.getCell(6).toString());
                }
                Double stockMid = null;
                if (checkValue(row.getCell(7).toString())) {
                    stockMid = Double.parseDouble(row.getCell(7).toString());
                }
                Double stockWest = null;
                if (checkValue(row.getCell(8).toString())) {
                    stockWest = Double.parseDouble(row.getCell(8).toString());
                }
                String avgSaleDayOneDate = titleRow.getCell(9).getStringCellValue();   //"2023/7/12-2023/7/18";
                Double avgSaleDayOne = row.getCell(9).getNumericCellValue();
                String avgSaleDayTwoDate = titleRow.getCell(10).getStringCellValue(); //"2023/7/19-2023/7/28";
                Double avgSaleDayTwo = row.getCell(10).getNumericCellValue();
                String avgSaleDayThreeDate = titleRow.getCell(11).getStringCellValue(); // "2023/7/29-2023/7/31";
                Double avgSaleDayThree = row.getCell(11).getNumericCellValue();
                String avgSaleDayFoureDate = titleRow.getCell(12).getStringCellValue(); //"2023/8/1-2023/8/7";
                Double avgSaleDayFoure = row.getCell(12).getNumericCellValue();
                String avgSaleDayFiveDate = titleRow.getCell(13).getStringCellValue(); //"2023/8/7-";
                Double avgSaleDayFive = row.getCell(13).getNumericCellValue();

                Double salesRatioEast = row.getCell(14).getNumericCellValue();
                Double salesRatioMid = row.getCell(15).getNumericCellValue();
                Double salesRatioWest = row.getCell(16).getNumericCellValue();


                Double revolveDayEast = row.getCell(17).getNumericCellValue();
                Double revolveDayMid = row.getCell(18).getNumericCellValue();
                Double revolveDayWest = row.getCell(19).getNumericCellValue();


                String dateEast = row.getCell(20).getStringCellValue();
                String dataMid = row.getCell(21).getStringCellValue();
                String dataWest = row.getCell(22).getStringCellValue();

                SkuCalcDate skuCalcDate = SkuCalcDate.builder().modelName(modelName).sku(sku).artNo(artNo).length(length).
                        width(width).height(height).stockEast(stockEast).stockMid(stockMid).stockWest(stockWest).salesRatioEast(salesRatioEast).salesRatioWest(salesRatioWest).
                        salesRatioMid(salesRatioMid).avgSaleDayOne(avgSaleDayOne).avgSaleDayTwo(avgSaleDayTwo).avgSaleDayThree(avgSaleDayThree).avgSaleDayFour(avgSaleDayFoure)
                        .avgSaleDayFive(avgSaleDayFive).revolveDayEast(revolveDayEast).revolveDayMid(revolveDayMid).revolveDayWest(revolveDayWest)
                        .avgSaleDayOneDate(avgSaleDayOneDate).avgSaleDayTwoDate(avgSaleDayTwoDate).avgSaleDayThreeDate(avgSaleDayThreeDate)
                        .avgSaleDayFourDate(avgSaleDayFoureDate).avgSaleDayFiveDate(avgSaleDayFiveDate)
                        .dateEast(dateEast).dateMid(dataMid).dateWest(dataWest).build();
                list.add(skuCalcDate);
                //map.put(sku, skuCalcDate);
                /*
                if (cell.getCellType() == CellType.STRING) {
                    String value = cell.getStringCellValue();
                    System.out.print(value);

                } else if (cell.getCellType() == CellType.NUMERIC) {
                    double value = cell.getNumericCellValue();
                    System.out.print(value);
                }*/
                //System.out.print("\t"); // 每个单元格之间加一个制表符
            }

        }

        fis.close();
        list.stream().sorted(Comparator.comparing(SkuCalcDate::getSku));
        System.out.println(JSONObject.toJSONString(list));
        return list;
    }


    private static Boolean checkValue(String stringCellValue) {
        return !"".equals(stringCellValue) && !"X".equals(stringCellValue);
    }


}
